/*
 * TreeDroid Database Adapter
 * 
 * Copyright (C) 2008 William W. Ferrell
 * 
 * This file is part of TreeDroid.
 *
 * TreeDroid is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * TreeDroid is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with TreeDroid.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

package com.willfe.TreeDroid;

import java.io.InputStream;
import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * TreeDroid database access helper class. Provides functions to list all 
 * databases, all nodes in a given database, all nodes from all databases, 
 * and supporting operations for adding, retrieving, editing, and deleting 
 * nodes.
 */
public class TreeDroidDbAdapter {

    private static final String TAG = "TreeDroidDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private static final String DATABASE_NAME = "treedroid";
    private static final String DATABASE_TABLE_TREES = "trees";
    private static final String DATABASE_TABLE_NODES = "nodes";
    private static final int DATABASE_VERSION = 1;

    /* The nodes table */
    public static final String KEY_NODE_TYPE = "type";
	public static final String KEY_NODE_TREE = "tree";
	public static final String KEY_NODE_PARENT = "parent";
	public static final String KEY_NODE_PRIORITY = "priority";
	public static final String KEY_NODE_STATUS = "status";
	public static final String KEY_NODE_EXPANDED = "expanded";
	public static final String KEY_NODE_CREATED = "created";
	public static final String KEY_NODE_DUE = "due";
	public static final String KEY_NODE_COMPLETED = "completed";
	public static final String KEY_NODE_TITLE = "title";
    public static final String KEY_NODE_BODY = "body";
    
    /* The trees table */
    public static final String KEY_TREE_NAME = "name";
    
    /* Used in all tables */
    public static final String KEY_ROWID = "_id";

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {
    	private Context ctx;
    	
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            this.ctx = context;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        	/* Create the trees table */
        	db.execSQL("CREATE TABLE trees (_id INTEGER PRIMARY KEY NOT NULL, name TEXT);");
        	
        	/* Create tree indices */
        	db.execSQL("CREATE INDEX trees_names ON trees(name);");
        	
        	/* Load initial trees table data */
        	db.execSQL("INSERT INTO trees (_id, name) VALUES (1, 'Default');");
        	        	
        	/* Create the nodes table */
        	db.execSQL("CREATE TABLE nodes ( " +
        			"_id INTEGER PRIMARY KEY NOT NULL,  " +
        			"type INTEGER DEFAULT 0,  " +
        			"tree INTEGER, " +
        			"parent INTEGER DEFAULT 0,  " +
        			"priority INTEGER DEFAULT 0,  " +
        			"status INTEGER DEFAULT 0,  " +
        			"expanded INTEGER DEFAULT 0, " +
        			"created INTEGER DEFAULT CURRENT_TIMESTAMP,  " +
        			"due INTEGER DEFAULT 0,  " +
        			"completed INTEGER DEFAULT 0, " +
        			"title TEXT DEFAULT 'Empty Node',  " +
        			"body TEXT DEFAULT NULL " +
        		");"
        	);
        	
        	/* Create node indices */
        	db.execSQL("CREATE INDEX nodes_completeds ON nodes(completed ASC);");
        	db.execSQL("CREATE INDEX nodes_created ON nodes(created ASC);");
        	db.execSQL("CREATE INDEX nodes_dues ON nodes(due ASC);");
        	db.execSQL("CREATE INDEX nodes_priorities ON nodes(priority ASC);");
        	db.execSQL("CREATE INDEX nodes_statuses ON nodes(status ASC);");
        	db.execSQL("CREATE INDEX nodes_titles ON nodes(title ASC);");
        	db.execSQL("CREATE INDEX nodes_types ON nodes(type ASC);");

        	/* Load initial nodes table data */
        	db.execSQL("INSERT INTO nodes (_id, tree, title, body) " +
        			"VALUES (1, 0, 'Welcome to TreeDroid!', 'You can use TreeDroid to create everything from a simple to-do list to a complex project with groups of tasks and subtasks, each with its own priority, due date, and status. Press [MENU] and select Help for more usage information and some examples of what you can do with TreeDroid.\n\n" +
        			"TreeDroid supports any number trees, and each tree supports any number of nodes, limited only by the memory capacity of your device.\n\n" +
        			"You can safely delete this node (or simply rename it and change or remove this note) at any time. You can also safely rename or delete the \"Default\" tree.');"
        	);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Would upgrade database from version " + oldVersion + " to "
                    + newVersion);
            /* onCreate(db); */
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public TreeDroidDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the nodes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public TreeDroidDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        mDbHelper.close();
    }
  
    /** Create a new tree using the title provided.
     * 
     * @param name tree name to create
     * @return rowId or -1 if failed
     * 
     */
    public long createTree(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TREE_NAME, name);
        	
        return mDb.insert(DATABASE_TABLE_TREES, null, initialValues);
    }
    
    /**
     * Create a new node using the values provided. If the node is successfully
     * created return the new rowId for that node, otherwise return a -1 to 
     * indicate failure.
     * 
     * @param type the node's type
     * @param tree the tree the node belongs to; 0 for normal, 1 for simple todo,
     *  2 for percent todo
     * @param parent the node's parent (0 for root)
     * @param priority the node's priority (1-5; 1 high priority, 5 low priority)
     * @param status the node's status, depending on type (for normal nodes, value is
     *  ignored; for simple todo nodes, 0 is unfinished, 1 is finished; for percent
     *  todo nodes, percentage completed, 0-100)
     * @param expanded 0 if the node's view is expanded to show children, 1 if collapsed
     * @param due the node's due date (if any; optional)
     * @param completed the node's completed date (if any; optional)
     * @param title the node's title
     * @param body the node's body
     * @return rowId or -1 if failed
     */
    public long createNode(int type, int tree, int parent, int priority, 
    	int status, int expanded, int due, int completed, String title, 
    	String body) {
    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NODE_TYPE, type);
        initialValues.put(KEY_NODE_TREE, tree);
        initialValues.put(KEY_NODE_PARENT, parent);
        initialValues.put(KEY_NODE_PRIORITY, priority);
        initialValues.put(KEY_NODE_STATUS, status);
        initialValues.put(KEY_NODE_EXPANDED, expanded);
        initialValues.put(KEY_NODE_DUE, due);
        initialValues.put(KEY_NODE_COMPLETED, completed);
        initialValues.put(KEY_NODE_TITLE, title);
        initialValues.put(KEY_NODE_BODY, body);
    	
        return mDb.insert(DATABASE_TABLE_NODES, null, initialValues);
    }

    /**
     * Delete the node with the given rowId
     * 
     * @param rowId id of node to delete
     * @param recurse If true, delete node and all children; if false, 
     * 	reparent children to node's parent then delete node
     * @return true if deleted, false otherwise
     */
    public boolean deleteNode(long rowId, boolean recurse) {
    	/* This needs to handle re-parenting and recursive deletions
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;*/
    	return false;
    }

    /**
     * Return a Cursor over the list of all trees in the database
     * 
     * @return Cursor over all trees
     */
    public Cursor fetchTrees() {
    	return mDb.query(DATABASE_TABLE_TREES, null, null, null, null, null, null);
    }
        
    /**
     * Return a Cursor over the list of all nodes in the database
     * 
     * @param parent ID of parent node (0 to list root-level nodes)
     * @param sort Column to sort by (passed to SQLite; any valid SQL sort order
     *  can be used)
     * @return Cursor over all retrieved nodes
     */
    public Cursor fetchNodes(int parent, String sort) {
    	/* Yes, this usage is "discouraged" by the documentation. However, in this
    	 * application, all columns really are needed/useful here. */
    	return mDb.query(DATABASE_TABLE_NODES, null, KEY_ROWID + "=" + parent, null, null, null, sort);
    }

    /**
     * Return a Cursor positioned at the tree that matches the given rowId
     * 
     * @param rowId id of tree to retrieve
     * @return Cursor positioned to matching tree, if found
     * @throws SQLException if node could not be found/retrieved
     */

    public Cursor fetchTree(long rowId) throws SQLException {
        Cursor mCursor = mDb.query(true, DATABASE_TABLE_TREES, 
        	null, KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    /**
     * Return a Cursor positioned at the node that matches the given rowId
     * 
     * @param rowId id of node to retrieve
     * @return Cursor positioned to matching node, if found
     * @throws SQLException if node could not be found/retrieved
     */
    public Cursor fetchNode(long rowId) throws SQLException {
        Cursor mCursor = mDb.query(true, DATABASE_TABLE_NODES, 
        	null, KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    /**
     * Update the node using the details provided.
     * 
     * @param rowId id of node to update
     * @param type the node's type
     * @param tree the tree the node belongs to; 0 for normal, 1 for simple todo,
     *  2 for percent todo
     * @param parent the node's parent (0 for root)
     * @param priority the node's priority (1-5; 1 high priority, 5 low priority)
     * @param status the node's status, depending on type (for normal nodes, value is
     *  ignored; for simple todo nodes, 0 is unfinished, 1 is finished; for percent
     *  todo nodes, percentage completed, 0-100)
     * @param expanded 0 if the node's view is expanded to show children, 1 if collapsed
     * @param due the node's due date (if any; optional)
     * @param completed the node's completed date (if any; optional)
     * @param title the node's title
     * @param body the node's body
     */
    public boolean updateNode(long rowId, int type, int tree, int parent, 
    	int priority, int status, int expanded, int created, int due, 
    	int completed, String title, String body) {

        ContentValues values = new ContentValues();
        values.put(KEY_NODE_TYPE, type);
        values.put(KEY_NODE_TREE, tree);
        values.put(KEY_NODE_PARENT, parent);
        values.put(KEY_NODE_PRIORITY, priority);
        values.put(KEY_NODE_STATUS, status);
        values.put(KEY_NODE_EXPANDED, expanded);
        values.put(KEY_NODE_DUE, due);
        values.put(KEY_NODE_COMPLETED, completed);
        values.put(KEY_NODE_TITLE, title);
        values.put(KEY_NODE_BODY, body);
       
        return mDb.update(DATABASE_TABLE_NODES, values, KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Update the tree using the details provided. 
     * 
     * @param rowId id of node to update
     * @param name new name to use for the tree
     * 
     */
    public boolean updateTree(long rowId, String name) {
        ContentValues values = new ContentValues();
        values.put(KEY_TREE_NAME, name);
       
        return mDb.update(DATABASE_TABLE_TREES, values, KEY_ROWID + "=" + rowId, null) > 0;
    }
}
